insert overwrite table jms_dm.dm_ops_xyf_reverse_cancel_hi partition ( dt )
select
    '{{ execution_date | cst_ds }}' as date_time --时间
   ,t.pick_network_code               --取件网点
   ,t1.name  as pick_network_name     --取件网点name
   ,t.proxy_area_code                 --取件代理区
   ,t.proxy_area_name                 --取件代理区code
   ,sum(case when t.order_status_code='103' then 1 else 0 end ) as pick_num       --已取件
   ,sum(case when t.order_status_code='104' and nvl((unix_timestamp(cancel_time,'yyyy-MM-dd HH:mm:ss') - unix_timestamp(input_time,'yyyy-MM-dd HH:mm:ss'))/60,0)>5  then 1 else 0 end ) as cancel_num     --已取消
   ,sum(case when t.order_status_code in ('100','106','101','102') then 1 else 0 end ) as   wait_pick_num--待取件
   ,count(distinct t.waybill_id) as bill_num  --单量
   ,if(substring('{{ execution_date | cst_hour }}', 12, 2) = '15' ,'{{ execution_date | cst_ds }}','{{ execution_date | cst_hour }}' ) as dt
from jms_dwd.dwd_yl_oms_oms_order_hf  t 
left join jms_dim.dim_network_whole_massage t1 on t.pick_network_code = t1.code
where  t.dt >= '{{ execution_date | date_add(-1)| cst_ds }}-16' and t.dt <=  '{{ execution_date | cst_ds }}-15'
       and t.order_source_code='D957'
group by     
    t.pick_network_code 
   ,t1.name  
   ,t.proxy_area_code
   ,t.proxy_area_name
  distribute by dt,pmod(hash(pick_network_code), 5)
;   
   